<?php

namespace Sys\Library;

use PDO;
use PDOException;
use Exception;

/**
 * pdo数据库连接类
 */
class Connection
{
    /**
     * pdo 实例
     *
     * @var PDO
     */
    protected $pdo;
    /**
     * 预处理对象
     * @var
     */
    protected $sQuery;
    /**
     * 条件集合
     * @var array
     */
    protected $where = array();
    /**
     * sql 的参数
     *
     * @var array
     */
    protected $settings = array();
    /**
     * 最后一条直行的 sql
     *
     * @var string
     */
    protected $lastSql = '';

    /**
     * 是否执行成功
     *
     * @var bool
     */
    protected $success = false;
    /**
     * 是否是更新
     *
     * @var bool
     */
    protected $for_update = false;

    /**
     * 表名
     * @var string
     */
    protected $table = '';
    /**
     * 前缀
     * @var string
     */
    protected $prefix = '';

    /**
     * ORDER BY 的列
     *
     * @var array
     */
    protected $order_by = array();
    /**
     * GROUP BY 的列
     *
     * @var array
     */
    protected $group_by = array();

    /**
     * HAVING 条件数组.
     *
     * @var array
     */
    protected $having = array();

    /**
     * 每页多少条记录
     *
     * @var int
     */
    protected $paging = 10;
    /**
     * 设置limit
     * @var int
     */
    protected $limit = 0;
    /**
     * 返回记录的游标
     *
     * @var int
     */
    protected $offset = 0;

    /**
     * ORDER BY 的排序方式,默认为升序
     *
     * @var bool
     */
    protected $order_asc = true;
    /**
     * 返回数据型式,所有,还是列,还是单元个
     * @var string
     */
    protected $read_type = '';
    /**
     * 执行类型
     * @var string
     */
    protected $type = '';
    /**
     * 是否返回一条SQL
     * @var bool
     */
    protected $return_sql = false;
    /**
     * 选择的列
     *
     * @var array
     */
    protected $cols = array();
    /**
     * INSERT 或者 UPDATE 的列
     *
     * @param array
     */
    protected $col_values;

    /**
     * 构造函数
     * Connection constructor.
     * @param $host
     * @param $port
     * @param $user
     * @param $password
     * @param $db_name
     * @param $prefix
     * @param string $charset
     *
     */
    public function __construct($host, $port, $user, $password, $db_name, $prefix, $charset = 'utf8')
    {
        $this->settings = array(
            'host'     => $host,
            'port'     => $port,
            'user'     => $user,
            'password' => $password,
            'dbname'   => $db_name,
            'prefix'   => $prefix,
            'charset'  => $charset,
        );

        try {
            $this->connect();
        } catch (\Exception $e) {
            exit($this->setJson($e->getCode(), $e->getMessage(), $e->getLine()));
        }

    }

    /**
     * 创建 PDO 实例
     */
    protected function connect()
    {
        if (!extension_loaded('pdo_mysql')) {
            exit(setJson(500, 'pdo_mysql extension not install,pdo_mysql扩展未安装'));
        }
        $dsn       = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' .
            $this->settings["host"] . ';port=' . $this->settings['port'];
        $this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"],
            array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . (!empty($this->settings['charset']) ?
                        $this->settings['charset'] : 'utf8')
            ));
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $this->prefix = $this->settings['prefix'];
    }

    /**
     * 关闭数据库
     */
    public function closeConnection()
    {
        $this->pdo = null;
    }

    /**
     * 返回 lastInsertId
     *
     * @return string
     */
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * 返回最后一条执行的 sql
     *
     * @return  string
     */
    public function lastSQL()
    {
        return $this->lastSql;
    }

    /**
     * 设置需要查询哪些字段
     * @param string $cols
     * @return $this
     */
    public function select($cols = '*')
    {
        if ($cols == '*') {
            return $this;
        }
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->cols[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->cols[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置表名
     * @param $table
     * @param bool $autoPrefix
     * @return $this
     */
    public function table($table, $autoPrefix = true)
    {
        if ($autoPrefix) {
            $this->table = $this->prefix . $table;
        } else {
            $this->table = $table;
        }
        $this->table = $this->parseKey($this->table);
        return $this;
    }

    /**
     * 拼接and的条件
     * @param $field
     * @param $condition
     * @param null $value
     * @return $this
     */
    public function where($field, $condition, $value = null)
    {
        if (!is_string($field) || empty($field)) {
            return $this;
        }
        if ($value === null) {
            $value     = $condition;
            $condition = '=';
        }
        if (count($this->where) == 0) {
            $this->where[] = ' WHERE ' . $this->createWhereString($field, $condition, $value);;
        } else {
            $this->where[] = 'AND ' . $this->createWhereString($field, $condition, $value);
        }
        return $this;
    }

    /**
     * 拼接or的条件
     * @param $field
     * @param $condition
     * @param null $value
     * @return $this
     */
    public function orWhere($field, $condition, $value = null)
    {
        if (!is_string($field) || empty($field)) {
            return $this;
        }
        if ($value === null) {
            $value     = $condition;
            $condition = '=';
        }
        if (count($this->where) == 0) {
            $this->where[] = $this->createWhereString($field, $condition, $value);;
        } else {
            $this->where[] = 'OR ' . $this->createWhereString($field, $condition, $value);
        }
        return $this;
    }

    /**
     * 使用原始字符串条件
     * @param $raw
     * @return $this
     */
    public function whereRaw($raw)
    {
        if (is_array($raw)) {
            return $this;
        }
        $this->where[] = $raw;
        return $this;
    }

    public function setAll()
    {
        $this->type      = 'SELECT';
        $this->read_type = 'ALL';
        return $this;
    }


    /**
     * 设置返回一行数据
     * @return $this
     */
    public function setRow()
    {
        $this->type      = 'SELECT';
        $this->read_type = 'ROW';
        return $this;
    }

    /**
     * 设置返回一列数据
     * @return $this
     */
    public function setCol()
    {
        $this->type      = 'SELECT';
        $this->read_type = 'COL';
        return $this;
    }

    /**
     * 设置返回一行一列数据
     * @return $this
     */
    public function setOne()
    {
        $this->type      = 'SELECT';
        $this->read_type = 'ONE';
        return $this;
    }

    /**
     * 返回多少行
     * @return $this
     */
    public function setCount()
    {
        $this->type      = 'SELECT';
        $this->read_type = 'COUNT';
        return $this;
    }

    /**
     * 设置删除
     * @return $this
     */
    public function setDelete()
    {
        $this->type = 'DELETE';
        return $this;
    }

    /**
     * 设置更新
     * @return \WorkerIm\Connection
     */
    public function setUpdate()
    {
        $this->type = 'UPDATE';
        return $this;
    }

    /**
     * 设置新增
     * @return $this
     */
    public function setInsert()
    {
        $this->type = 'INSERT';
        return $this;
    }

    /**
     * 是否返回当前SQL语句
     * @param bool $bool
     * @return $this
     */
    public function fetchSql($bool = true)
    {
        $this->return_sql = $bool;
        return $this;
    }


    /**
     * 终结方法
     * @return $this;
     */
    public function query()
    {
        try {

            $query         = $this->build();
            $this->lastSql = $query;
            $read_type     = strtolower($this->read_type);
            if ($this->return_sql) {
                $this->resetAll();
                return $this->lastSql;
            }
            $this->resetAll();
            $this->execute($query);
            $rawStatement = explode(" ", $query);
            $statement    = strtolower(trim($rawStatement[0]));
            if ($statement === 'select' || $statement === 'show') {
                switch ($read_type) {
                    case 'row':
                        return $this->getRow();
                        break;
                    case 'col':
                        return $this->getColumns();
                        break;
                    case 'one':
                        return $this->getOne();
                    case 'count':
                        return $this->getCount();
                    default:
                        return $this->getAll();
                        break;
                }
            } elseif ($statement === 'update' || $statement === 'delete') {
//            return $this->sQuery->rowCount();
                return true;
            } elseif ($statement === 'insert') {
                if ($this->sQuery->rowCount() > 0) {
                    return $this->lastInsertId();
                }
            } else {
                return null;
            }
        } catch (Exception $e) {
            exit($this->setJson($e->getCode(), $e->getMessage()));
        }
    }

    /**
     * 生成完整 SQL 语句
     *
     * @return string
     * @throws Exception
     */
    protected function build()
    {
        switch ($this->type) {
            case 'DELETE':
                return $this->buildDELETE();
            case 'INSERT':
                return $this->buildINSERT();
            case 'UPDATE':
                return $this->buildUPDATE();
            default:
                return $this->buildSELECT();
                break;
        }
    }

    /**
     * 返回一列数据
     * @param null $query
     * @return array|null
     */
    public function getColumns($query = null)
    {
        if ($query !== null) {
            $this->execute($query);
        }
        return $this->sQuery->fetchAll(PDO::FETCH_COLUMN, 0);
    }

    /**
     * 返回所有数据
     * @param null $query
     * @param int $fetchMode
     * @return array
     */
    public function getAll($query = null, $fetchMode = PDO::FETCH_ASSOC)
    {
        if ($query !== null) {
            $this->execute($query);
        }
        return $this->sQuery->fetchAll($fetchMode);
    }

    /**
     * 返回一行数据
     * @param null $query
     * @param int $fetchMode
     * @return mixed
     */
    public function getRow($query = null, $fetchMode = PDO::FETCH_ASSOC)
    {
        if ($query !== null) {
            $this->execute($query);
        }
        return $this->sQuery->fetch($fetchMode);
    }

    /**
     * 返回单个值
     * @param null $query
     * @return mixed
     */
    public function getOne($query = null)
    {
        if ($query !== null) {
            $this->execute($query);
        }
        return $this->sQuery->fetchColumn();
    }

    /**
     * 获取多少行数据
     * @param null $query
     * @return int
     */
    public function getCount($query = null)
    {
        if ($query !== null) {
            $this->execute($query);
        }
        return $this->sQuery->rowCount();
    }

    /**
     * 执行SQL
     * @param $query
     * @param string $parameters
     */
    protected function execute($query, $parameters = '')
    {
        try {
            $this->lastSql = $query;
            $this->sQuery  = @$this->pdo->prepare($query);
            $this->success = $this->sQuery->execute();
        } catch (PDOException $e) {
            // 服务端断开时重连一次
            if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {
                $this->closeConnection();
                $this->connect();

                try {
                    $this->sQuery  = $this->pdo->prepare($query);
                    $this->success = $this->sQuery->execute();
                } catch (PDOException $ex) {
                    $this->rollBackTrans();
                    throw $ex;
                }
            } else {
                $this->rollBackTrans();
                $msg       = $e->getMessage();
                $err_msg   = "SQL:" . $this->lastSQL() . " " . $msg;
                $exception = new \PDOException($err_msg, (int)$e->getCode());
                throw $exception;
            }
        }
    }

    /**
     * 直接设置列的值
     * @param $cols string|array 字段名称
     * @param null $value 值
     * @return $this 返回当前对象
     */
    public function set($cols, $value = null)
    {

        if ($value === null && is_array($cols)) {
            foreach ($cols as $colKey => $colValue) {
                $this->col_values[$this->parseKey($colKey)] = $this->quoteNamesIn($colValue);
            }
        } else {
            $this->col_values[$this->parseKey($cols)] = $this->quoteNamesIn($value);
        }
        return $this;
    }


    /**
     * 创建 DELETE SQL
     */
    protected function buildDELETE()
    {
        return 'DELETE'
            . $this->buildFrom()
            . $this->buildWhere()
            . $this->buildOrderBy()
            . $this->buildLimit();
    }

    /**
     * 生成 INSERT 语句
     *
     * @return string
     */
    protected function buildINSERT()
    {
        return 'INSERT'
            . $this->buildInto()
            . $this->buildValuesForInsert();
    }

    /**
     * 为 INSERT 语句绑定值
     *
     * @return string
     */
    protected function buildValuesForInsert()
    {
        return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' .
            $this->indentCsv(array_values($this->col_values)) . ')';
    }

    /**
     * 生成 INTO 语句
     *
     * @return string
     */
    protected function buildInto()
    {
        return " INTO " . $this->table;
    }

    /**
     * 生成更新的 SQL 语句
     */
    protected function buildUPDATE()
    {
        return 'UPDATE'
            . $this->buildTable()
            . $this->buildValuesForUpdate()
            . $this->buildWhere()
            . $this->buildOrderBy()
            . $this->buildLimit();
    }

    /**
     * 为更新语句绑定值
     *
     * @return string
     */
    protected function buildValuesForUpdate()
    {
        $values = array();
        foreach ($this->col_values as $col => $value) {
            $values[] = "{$col} = {$value}";
        }
        return ' SET' . $this->indentCsv($values);
    }

    /**
     * 哪个表
     *
     * @return string
     */
    protected function buildTable()
    {
        return " {$this->table}";
    }

    /**
     * 创建 SELECT SQL
     *
     * @return string
     */
    protected function buildSELECT()
    {
        return 'SELECT'
            . $this->buildCols()
            . $this->buildFrom()
            . $this->buildWhere()
            . $this->buildGroupBy()
            . $this->buildHaving()
            . $this->buildOrderBy()
            . $this->buildLimit()
            . $this->buildForUpdate();
    }

    protected function buildCols()
    {
        if (!$this->cols) {
            return ' *';
        }
        return $this->indentCsv($this->cols);
    }

    protected function buildFrom()
    {
        return ' from ' . $this->table;
    }

    protected function buildWhere()
    {
        if (!$this->where) {
            return '';
        }
        return implode(' ', $this->where);
    }

    protected function buildGroupBy()
    {
        if (!$this->group_by) {
            return '';
        }
        return ' group by' . $this->indentCsv($this->group_by);
    }

    protected function buildHaving()
    {
        if (!$this->having) {
            return '';
        }
        return ' having' . $this->indentCsv($this->having);
    }

    protected function buildOrderBy()
    {
        if (!$this->order_by) {
            return '';
        }
        if ($this->order_asc) {
            return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC';
        } else {
            return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC';
        }
    }

    protected function buildLimit()
    {
        $has_limit  = $this->type == 'DELETE' || $this->type == 'UPDATE';
        $has_offset = $this->type == 'SELECT' || $this->type == '';
        if ($has_offset && $this->limit) {
            $clause = " LIMIT {$this->limit}";
            if ($this->offset) {
                $clause .= " OFFSET {$this->offset}";
            }
            return $clause;
        } elseif ($has_limit && $this->limit) {
            return " LIMIT {$this->limit}";
        }
        return '';
    }

    /**
     * 生成 FOR UPDATE 语句
     *
     * @return string
     */
    protected function buildForUpdate()
    {
        if (!$this->for_update) {
            return '';
        }
        return ' FOR UPDATE';
    }

    /**
     * 清除所有数据
     */
    protected function resetAll()
    {
        $this->for_update = false;
        $this->cols       = array();
        $this->group_by   = array();
        $this->having     = array();
        $this->paging     = 10;
        $this->where      = array();
        $this->order_by   = array();
        $this->col_values = array();
        $this->limit      = 0;
        $this->offset     = 0;
        $this->table      = '';
        $this->return_sql = false;
        $this->order_asc  = true;
        $this->read_type  = '';
        $this->type       = '';
        $this->sQuery     = null;
    }

    /**
     * 设置 FOR UPDATE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function forUpdate($enable = true)
    {
        $this->for_update = (bool)$enable;
        return $this;
    }

    /**
     * 设置分组
     * @param $cols
     * @return $this
     */
    public function groupBy($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->group_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->group_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置排序
     * @param $cols
     * @return $this
     */
    public function orderBy($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->order_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->order_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置倒序排序
     * @param $cols
     * @return $this
     */
    public function orderByDESC($cols)
    {
        $this->order_asc = false;
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->order_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->order_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置limit
     * @param int $limit
     * @return $this
     */
    public function limit($limit = 1)
    {
        $this->limit = (int)$limit;
        return $this;
    }

    /**
     * 设置having
     * @param $col
     * @return $this
     */
    public function having($col)
    {
        if (!empty($col)) {
            $this->having[] = $col;
        }
        return $this;
    }

    /**
     * 设置每页的记录数量
     * @param $page
     * @param int $paging
     * @return $this
     */
    public function page($page, $paging = 0)
    {
        $this->limit  = 0;
        $this->offset = 0;
        if ($paging > 0) {
            $this->paging = $paging;
        }
        $page = (int)$page;
        if ($page > 0) {
            $this->limit  = $this->paging;
            $this->offset = $this->paging * ($page - 1);
        }
        return $this;
    }

    /**
     * 开始事务
     */
    public function beginTrans()
    {
        try {
            return $this->pdo->beginTransaction();
        } catch (PDOException $e) {
            // 服务端断开时重连一次
            if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {
                $this->closeConnection();
                $this->connect();
                return $this->pdo->beginTransaction();
            } else {
                throw $e;
            }
        }
    }

    /**
     * 提交事务
     */
    public function commitTrans()
    {
        return $this->pdo->commit();
    }

    /**
     * 事务回滚
     */
    public function rollBackTrans()
    {
        if ($this->pdo->inTransaction()) {
            return $this->pdo->rollBack();
        }
        return true;
    }
    //--------------------


    /**
     * Quotes "table.col" 格式的字符串
     *
     * @param string $text
     * @return string|array
     */
    public function quoteNamesIn($text)
    {
        if ($text === '') {
            return "''";
        }
        if (preg_match("/^[0-9]{1,11}$/", $text)) {
            return $text;
        }
        if ($text[0] == '"' && $text[strlen($text) - 1] == '""') {
            return $text;
        } elseif ($text[0] == "'" && $text[strlen($text) - 1] == "'") {
            return $text;
        } else {
            $text = addslashes($text);
            return "'{$text}'";
        }
    }

    /**
     * 返回逗号分隔的字符串
     *
     * @param array $list
     * @return string
     */
    protected function indentCsv(array $list)
    {
        return ' ' . implode(',', $list);
    }

    /**
     * 拼接where字符串
     * @param $field
     * @param $condition
     * @param null $value
     * @return $this|string
     */
    private function createWhereString($field, $condition, $value = null)
    {
        if ($value === null) {
            $value     = $condition;
            $condition = '=';
        }
        $condition = strtolower($condition);
        if (is_array($value)) {
            $value = join(',', $value);
        } else {
            $value = $this->quoteNamesIn($value);
        }
        $field = $this->parseKey($field);
        switch ($condition) {
            case '=':
            case '!=':
            case '<>':
            case '>':
            case '>=':
            case '<':
            case '<=':
                $string = $field . $condition . $value;
                break;
            case 'in':
                $string = $field . ' in (' . $value . ')';
                break;
            case 'notin':
            case 'not in':
                $string = $field . ' not in (' . $value . ')';
                break;
            case 'like':
                $string = $field . ' like ' . $value;
                break;
            case 'notlike':
            case 'not like':
                $string = $field . ' not like ' . $value;
                break;
            case 'instr':
                $string = 'instr($field,' . $value . ')';
                break;
            default:
                $string = $field . $condition . $value;
                break;
        }
        return $string;
    }

    /**
     * 构建表名
     * @param $table
     * @param $prefix
     * @return string
     */
    private function parseTable($table, $prefix)
    {
        return $this->parseKey($this->db_name) . '.' . $this->parseKey($prefix . $table);
    }

    /**
     * 字段处理
     * @access protected
     * @param string $key
     * @return string
     */
    protected function parseKey($key)
    {
        $key = trim($key);
        if (!is_numeric($key) && !preg_match('/[,\'\"\*\(\)`.\s]/', $key)) {
            $key = '`' . $key . '`';
        }
        return $key;
    }

    /**
     * 字段值处理
     * @access protected
     * @param string $val
     * @return string
     */
    protected function parseValue($val)
    {
        $val = trim($val);
        if (!is_numeric($val) && !preg_match('/[,\'\"\*\(\)`.\s]/', $val)) {
            $val = "'" . $val . "'";
        }
        return $val;
    }

    /**
     * 设置返回信息
     * @param $status
     * @param $msg
     * @param $data
     * @return string
     */
    protected function setJson($status, $msg, $data = null)
    {
        return json_encode(array(
            'status' => $status,
            'msg'    => $msg,
            'data'   => $data,
        ));
    }
}